package com.springboot.film.dao;

import com.springboot.film.entity.userinfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;

@Component(value = "userinfoDao")
public interface userinfoDao {

    //用户信息：userinfo

    /**
     * 显示总数量
     * @return
     */
    @Select("select count(*) from userinfo")
    int getCount();

    /**
     * 全部分页显示
     * @return
     */
    @Select("select a.*,(case a.vip when 0 then '普通用户' else '会员用户' end) as vip_temp," +
            "(select b.name from films b where a.fid1=b.id) as fname1," +
            "(select b.name from films b where a.fid2=b.id) as fname2," +
            "(select b.name from films b where a.fid3=b.id) as fname3," +
            "(select b.name from films b where a.fid4=b.id) as fname4 " +
            "from userinfo a limit #{pos},#{size}")
    List<userinfo> findByPage(@Param("pos")int pos, @Param("size")int size);

    /**
     * 显示搜索的总数量
     * @return
     */
    @Select("select count(*) from userinfo a where ${key} like concat(concat('%',#{name}),'%')")
    int getsearchCount(@Param("key")String key,@Param("name")String name);

    /**
     * 搜索并分页显示
     * @return
     */
    @Select("select a.*,(case a.vip when 0 then '普通用户' else '会员用户' end) as vip_temp," +
            "(select b.name from films b where a.fid1=b.id) as fname1," +
            "(select b.name from films b where a.fid2=b.id) as fname2," +
            "(select b.name from films b where a.fid3=b.id) as fname3," +
            "(select b.name from films b where a.fid4=b.id) as fname4 " +
            "from userinfo a where ${key} like concat(concat('%',#{name}),'%') limit #{pos},#{size}")
    List<userinfo> findsearchByPage(@Param("key")String key, @Param("name")String name, @Param("pos")int pos, @Param("size")int size);

    /**
     * 删除
     */
    @Delete("DELETE FROM userinfo WHERE ID=#{id}")
    void delete(@Param("id")int id);

    /**
     * 修改(先找到并显示，再修改)
     */
    @Select("select * from userinfo where id=#{id}")
    List<userinfo> getUserone(@Param("id")int id);

    @Update("update userinfo set name=#{name},password=#{password},email=#{email},fid1=#{fid1}," +
            "fid2=#{fid2},fid3=#{fid3},fid4=#{fid4} where id=#{id}")
    void update(userinfo userinfo);

    /**
     * 前台操作
     */
    @Select("select count(*) from userinfo where name=#{name} and password=#{password}")
    int checkone(@Param("name")String name,@Param("password")String password);

    @Select("select id from userinfo where name=#{name}")
    int getuid(@Param("name")String name);

    @Insert("INSERT INTO userinfo(name,password,email) VALUES(#{name},#{password},#{email})")
    void add(userinfo userinfo);

    @Select("select a.*,(case a.vip when 0 then '普通用户' else '会员用户' end) as vip_temp," +
            "if(a.vip=1,(select FROM_UNIXTIME(b.end_time, '%Y-%m-%d') as end_time_temp from " +
            "userinfo a,vipinfo b where a.id=b.uid),0) as end_time_temp from userinfo a,vipinfo b where a.id=#{id}")
    List<userinfo> getone(@Param("id")int id);

    @Select("select fid#{fid} from userinfo where id=#{id}")
    int getfid(@Param("id")int id,@Param("fid")int fid);

    @Update("update userinfo set fid1=#{fid1},fid2=#{fid2},fid3=#{fid3},fid4=#{fid4} where id=#{id}")
    void updatefid(@Param("fid1")int fid1,@Param("fid2")int fid2,@Param("fid3")int fid3,@Param("fid4")int fid4,@Param("id")int id);

    @Select("select vip from userinfo where name=#{name}")
    int getvip(@Param("name")String name);

}
